﻿Imports SS
Imports SS.Databases
Imports SS.Databases.Connecter
Imports SS.ApplicationBlock

Public Class Authentication

	Dim connOra As DBOracle

	Public Sub New()

	End Sub

	Public Sub New(ByVal conn As DBOracle)
		connOra = conn
	End Sub

    Public Sub New(ByVal Transaction As Boolean)
        connOra = New DBOracle(Common.Utility.ConnectString, Transaction)
    End Sub

    Public Sub Commit()
        connOra.Commit()
        connOra.Close()
    End Sub

    Public Sub RollBack()
        connOra.Rollback()
        connOra.Close()
    End Sub

	Public Function InsertUserRole(ByVal role As Entity.UserRowItem) As Boolean
		Dim strSql As String
		strSql = ORM.Broker.RetrieveInsert(role, Common.Utility.Schema & ".RNWM_MAIN_USER_ROLE")
		If connOra.ExecuteNonQuery(strSql) = 1 Then
			Return True
		Else
			Return False
		End If
	End Function

	Public Shared Function LoadReport(ByVal userId As String) As DataTable
		Return LoadFunction(userId, "R", Nothing)
	End Function

	Public Shared Function LoadDownload(ByVal userId As String) As DataTable
		Return LoadFunction(userId, "D", Nothing)
	End Function

    Public Shared Function LoadAdmin(ByVal userId As String) As DataTable
        Return LoadFunction(userId, "A", Nothing)
    End Function

	Public Shared Function LoadFunction(ByVal userId As String, ByVal unitID As String) As DataTable
		Return LoadFunction(userId, Nothing, unitID)
	End Function

	Public Shared Function LoadAutorizeEntity(ByVal roleId As String, ByVal key As String) As List(Of Entity.AuthorizationEntityItem)
		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Dim dt As DataTable = connOraSelect.GetDataTable("SELECT * FROM " & Common.Utility.Schema & ".RNWM_MAIN_PRIVILEGE_ENTITY WHERE ENTITY_KEY='" & key & "' AND ROLE_ID = '" & roleId & "'", "Bank")
			If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
				Dim objReturn As List(Of Entity.AuthorizationEntityItem)
				objReturn = New List(Of Entity.AuthorizationEntityItem)
				objReturn = SS.ORM.Broker.RetrieveInstance(Of Entity.AuthorizationEntityItem)(dt)
				Return objReturn
			Else
				Return Nothing
			End If
		End Using
	End Function

	Public Shared Function LoadFunction(ByVal userId As String, ByVal flag As String, ByVal unitID As String) As DataTable
		Dim sb As New Text.StringBuilder
		sb.AppendLine("SELECT RNWM_MAIN_FUNCTION_INFO.FUNCTION_NAME, ")
		sb.AppendLine("RNWM_MAIN_FUNCTION_INFO.FUNCTION_DESC, ")
		sb.AppendLine("RNWM_MAIN_FUNCTION_INFO.FUNCTION_URL")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_USER_INFO")
		sb.AppendLine("INNER JOIN ")
		sb.AppendLine("" & Common.Utility.Schema & ".RNWM_MAIN_USER_PRIV")
		sb.AppendLine("ON RNWM_MAIN_USER_INFO.USER_ID = RNWM_MAIN_USER_PRIV.USER_ID")
		sb.AppendLine("INNER JOIN")
		sb.AppendLine("" & Common.Utility.Schema & ".RNWM_MAIN_FUNCTION_INFO")
		sb.AppendLine("ON RNWM_MAIN_USER_PRIV.FUNCTION_ID = RNWM_MAIN_FUNCTION_INFO.FUNCTION_ID")
		sb.AppendLine("INNER JOIN ")
		sb.AppendLine("" & Common.Utility.Schema & ".RNWM_MAIN_PRIVILEGE_INFO")
		sb.AppendLine("ON RNWM_MAIN_USER_PRIV.PRIVILEGE_ID = RNWM_MAIN_PRIVILEGE_INFO.PRIVILEGE_ID")
		If Not String.IsNullOrEmpty(flag) Then
			sb.AppendLine("WHERE RNWM_MAIN_FUNCTION_INFO.FUNCTION_TYPE = '" & flag & "'")
		Else
			sb.AppendLine("WHERE 1=1 ")
		End If
		sb.AppendLine("AND RNWM_MAIN_FUNCTION_INFO.RECORD_STATUS = 'A' ")
		sb.AppendLine("AND RNWM_MAIN_PRIVILEGE_INFO.PRIVILEGE_ID = 'F'")
		sb.AppendLine("AND RNWM_MAIN_USER_INFO.USER_ID = '" & userId & "'")
		If Not String.IsNullOrEmpty(unitID) Then
			sb.AppendLine("AND RNWM_MAIN_FUNCTION_INFO.FUNCTION_ID = '" & unitID & "'")
		End If
		sb.AppendLine("UNION ")
		sb.AppendLine("SELECT RNWM_MAIN_FUNCTION_INFO.FUNCTION_NAME, ")
		sb.AppendLine("RNWM_MAIN_FUNCTION_INFO.FUNCTION_DESC, ")
		sb.AppendLine("RNWM_MAIN_FUNCTION_INFO.FUNCTION_URL")
		sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_USER_INFO")
		sb.AppendLine("INNER JOIN ")
		sb.AppendLine("" & Common.Utility.Schema & ".RNWM_MAIN_USER_ROLE")
		sb.AppendLine("ON RNWM_MAIN_USER_INFO.USER_ID = RNWM_MAIN_USER_ROLE.USER_ID")
		sb.AppendLine("INNER JOIN ")
		sb.AppendLine("" & Common.Utility.Schema & ".RNWM_MAIN_ROLE_PRIV")
		sb.AppendLine("ON RNWM_MAIN_USER_ROLE.ROLE_ID = RNWM_MAIN_ROLE_PRIV.ROLE_ID")
		sb.AppendLine("INNER JOIN")
		sb.AppendLine("" & Common.Utility.Schema & ".RNWM_MAIN_FUNCTION_INFO")
		sb.AppendLine("ON RNWM_MAIN_ROLE_PRIV.FUNCTION_ID = RNWM_MAIN_FUNCTION_INFO.FUNCTION_ID")
		sb.AppendLine("INNER JOIN ")
		sb.AppendLine("" & Common.Utility.Schema & ".RNWM_MAIN_PRIVILEGE_INFO")
		sb.AppendLine("ON RNWM_MAIN_ROLE_PRIV.PRIVILEGE_ID = RNWM_MAIN_PRIVILEGE_INFO.PRIVILEGE_ID")
		If Not String.IsNullOrEmpty(flag) Then
			sb.AppendLine("WHERE RNWM_MAIN_FUNCTION_INFO.FUNCTION_TYPE = '" & flag & "'")
		Else
			sb.AppendLine("WHERE 1=1 ")
		End If
		sb.AppendLine("AND RNWM_MAIN_FUNCTION_INFO.RECORD_STATUS = 'A' ")
		sb.AppendLine("AND RNWM_MAIN_PRIVILEGE_INFO.PRIVILEGE_ID = 'F'")
		sb.AppendLine("AND RNWM_MAIN_USER_INFO.USER_ID = '" & userId & "'")
		If Not String.IsNullOrEmpty(unitID) Then
			sb.AppendLine("AND RNWM_MAIN_FUNCTION_INFO.FUNCTION_ID = '" & unitID & "'")
		End If

		Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
			Return connOraSelect.GetDataTable(sb.ToString, "report")
		End Using

	End Function

	Public Shared Sub LogInLog(ByVal ipAddress As String, ByVal userId As String)
		Dim sb As New Text.StringBuilder
		sb.AppendLine("INSERT INTO " & Common.Utility.Schema & ".RNWT_MAIN_USER_LOGIN")
		sb.AppendLine("(HISTORY_ID, LOGIN_DATE,IP_ADDRESS,LOGOUT_DATE, USER_ID) VALUES (")
		sb.AppendLine("RNW_SEQ_HISTORY_ID.NEXTVAL,")
		sb.AppendLine("SYSDATE,")
		sb.AppendLine("'" & ipAddress & "',")
		sb.AppendLine("NULL,")
		sb.AppendLine("'" & userId & "'")
		sb.AppendLine(")")
		Using connOra As New DBOracle(Common.Utility.ConnectString, False)
			connOra.ExecuteNonQuery(sb.ToString)
		End Using
	End Sub

	Public Shared Sub LogOutLog(ByVal ipAddress As String, ByVal userId As String)

	End Sub

	Public Shared Sub LogInFailLog()

	End Sub

    Public Shared Function LoadUserIDInfo(ByVal officerappid As String) As DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT OFIF.OFFICER_ID, ")
        sb.AppendLine("OFIF.OFFICER_APP_ID, ")
        sb.AppendLine("OFIF.OFFICER_FIRSTNAME_THA, ")
        sb.AppendLine("OFIF.OFFICER_LASTNAME_THA, ")
        sb.AppendLine("USIN.USER_ID ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_OFFICER_INFO OFIF ")
        sb.AppendLine("INNER JOIN " & Common.Utility.Schema & ".RNWM_MAIN_USER_INFO USIN ON OFIF.OFFICER_ID=USIN.OFFCIER_ID ")
        sb.AppendLine("WHERE OFIF.OFFICER_APP_ID = '" & officerappid & "' ")

        Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
            Dim dt As DataTable = connOraSelect.GetDataTable(sb.ToString, "Data")
            If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
                Return dt
            Else
                Return Nothing
            End If
        End Using
    End Function

    Public Shared Function LoadFunctionAuthorize(ByVal userid As String) As DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT FUN.FUNCTION_ID, ")
        sb.AppendLine("FUN.FUNCTION_NAME, ")
        sb.AppendLine("FUN.FUNCTION_DESC, ")
        sb.AppendLine("(SELECT COUNT(SUSPR.FUNCTION_ID) FROM " & Common.Utility.Schema & ".RNWM_MAIN_USER_PRIV SUSPR ")
        sb.AppendLine("WHERE SUSPR.USER_ID='" & userid & "' ")
        sb.AppendLine("AND SUSPR.FUNCTION_ID=FUN.FUNCTION_ID) AS FUN_AUTH ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_FUNCTION_INFO FUN ")
        sb.AppendLine("LEFT JOIN " & Common.Utility.Schema & ".RNWM_MAIN_USER_PRIV USPR ON USPR.FUNCTION_ID=FUN.FUNCTION_ID ")
        sb.AppendLine("WHERE FUN.RECORD_STATUS = 'A' ")
        sb.AppendLine("GROUP BY FUN.FUNCTION_ID, ")
        sb.AppendLine("FUN.FUNCTION_NAME, ")
        sb.AppendLine("FUN.FUNCTION_DESC ")
        sb.AppendLine("ORDER BY FUN.FUNCTION_ID ")

        Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
            Dim dt As DataTable = connOraSelect.GetDataTable(sb.ToString, "Data")
            If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
                Return dt
            Else
                Return Nothing
            End If
        End Using
    End Function

    Public Shared Function LoadActiveRole(ByVal userid As String) As DataTable
        Dim sb As New Text.StringBuilder
        sb.AppendLine("SELECT RLIF.ROLE_ID, ")
        sb.AppendLine("RLIF.ROLE_NAME, ")
        sb.AppendLine("RLIF.ROLE_DESC, ")
        sb.AppendLine("(SELECT COUNT(SROLE.ROLE_ID) FROM " & Common.Utility.Schema & ".RNWM_MAIN_USER_ROLE SROLE ")
        sb.AppendLine("WHERE SROLE.USER_ID='" & userid & "' ")
        sb.AppendLine("AND SROLE.ROLE_ID=RLIF.ROLE_ID) AS ROLE_AUTH ")
        sb.AppendLine("FROM " & Common.Utility.Schema & ".RNWM_MAIN_ROLE_INFO RLIF ")
        sb.AppendLine("LEFT JOIN " & Common.Utility.Schema & ".RNWM_MAIN_USER_ROLE ROL ON ROL.ROLE_ID=RLIF.ROLE_ID ")
        sb.AppendLine("WHERE RLIF.RECORD_STATUS = 'A' ")
        sb.AppendLine("GROUP BY RLIF.ROLE_ID, ")
        sb.AppendLine("RLIF.ROLE_NAME, ")
        sb.AppendLine("RLIF.ROLE_DESC ")
        sb.AppendLine("ORDER BY RLIF.ROLE_ID ")

        Using connOraSelect As New DBOracle(Common.Utility.ConnectString, False)
            Dim dt As DataTable = connOraSelect.GetDataTable(sb.ToString, "Data")
            If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
                Return dt
            Else
                Return Nothing
            End If
        End Using
    End Function

    Public Function InsertUserPrivilege(ByVal info As Entity.UserPrivilegeItem) As Boolean
        Dim strSql As String
        strSql = ORM.Broker.RetrieveInsert(info, Common.Utility.Schema & ".RNWM_MAIN_USER_PRIV")
		If connOra.ExecuteNonQuery(strSql) = 1 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function DeleteUserRole(ByVal userid As String) As Boolean
        Dim strSql As String
        strSql = "DELETE FROM " & Common.Utility.Schema & ".RNWM_MAIN_USER_ROLE WHERE RNWM_MAIN_USER_ROLE.USER_ID='" & userid & "'"
        Try
            connOra.ExecuteNonQuery(strSql)
            Return True
        Catch ex As Exception
            Common.LogError.AddLogException(ex)
            Return False
        End Try
    End Function

    Public Function DeleteUserPrivilege(ByVal userid As String) As Boolean
        Dim strSql As String
        strSql = "DELETE FROM " & Common.Utility.Schema & ".RNWM_MAIN_USER_PRIV WHERE RNWM_MAIN_USER_PRIV.USER_ID='" & userid & "'"
        Try
            connOra.ExecuteNonQuery(strSql)
            Return True
        Catch ex As Exception
            Common.LogError.AddLogException(ex)
            Return False
        End Try
    End Function

End Class
